import os

import pymysql
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

from Database import settings, TablesInterface


def init():
    """
    初始化数据库连接以及数据库相关信息
    :return: InitData
    """

    # 数据库连接
    mysql = pymysql.connect(host=settings.MYSQL['host'], user=settings.MYSQL['user'],
                            password=settings.MYSQL['pass'],
                            db=settings.MYSQL['db'])
    # 检测创建表
    cursor = mysql.cursor()
    print('检测创建表。。。')
    # 运营者表
    cursor.execute('''
                        CREATE TABLE IF NOT EXISTS `{table_operator}` (
                             `id` INT(11) NOT NULL PRIMARY KEY auto_increment,
                             `name`  CHAR(40) NOT NULL comment '运营者',
                             `nickname`  CHAR(40) NOT NULL comment '运营者昵称',
                             `email`  CHAR(40) NOT NULL comment '运营者邮箱',
                             `create_time` timestamp not null default current_timestamp comment '注册时间'
                             )
                        '''.format(table_operator=TablesInterface.table_operator))
    # 网红记录表
    cursor.execute('''
                                CREATE TABLE IF NOT EXISTS `{celebrity}` (
                                     `id` INT(11) NOT NULL PRIMARY KEY auto_increment,
                                     `operator_id`  CHAR(40) NOT NULL comment '运营者',
                                     `source`  CHAR(20) NOT NULL comment '网红来源',
                                     `source_url`  CHAR(255) NOT NULL comment '网红来源URL',
                                     `nickname`  CHAR(20) comment '网红昵称',
                                     `email` VARCHAR(80) comment '网红邮件',  
                                     `fans` int(11) comment '粉丝数',
                                     `create_time` timestamp not null default current_timestamp comment '爬取时间'
                                     )
                                '''.format(celebrity=TablesInterface.table_celebrity))
    # 爬取url
    cursor.execute('''
                                CREATE TABLE IF NOT EXISTS `{crawled}` (
                                     `id` INT(11) NOT NULL PRIMARY KEY auto_increment,
                                     `url` TEXT NOT NULL comment '爬取URL',
                                     `create_time` timestamp not null default current_timestamp comment '爬取时间'
                                     )
                                '''.format(crawled=TablesInterface.table_crawled))
    # 爬取关键词
    cursor.execute('''
                                CREATE TABLE IF NOT EXISTS `{crawled_words_source}` (
                                     `id` INT(11) NOT NULL PRIMARY KEY auto_increment,
                                     `words` TEXT NOT NULL comment '搜索词',
                                     `page` int NOT NULL comment '爬取页码',
                                     `create_time` timestamp not null default current_timestamp comment '爬取时间'
                                     )
                                '''.format(crawled_words_source=TablesInterface.table_crawled_words_source))
    mysql.commit()
    cursor.close()

    return mysql


def get_flask_db(app: Flask):
    """
    初始化flask数据库
    :param app:
    :return:
    """
    pymysql.install_as_MySQLdb()

    # 设置连接数据库的URL
    app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://{user}:{password}@{host}:{port}/{db}'.format(
        user=settings.MYSQL['user'], password=settings.MYSQL['pass'], host=settings.MYSQL['host'],
        port=settings.MYSQL['port'], db=settings.MYSQL['db']
    )

    # 数据库和模型类同步修改
    app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True

    # 查询时会显示原始SQL语句
    app.config['SQLALCHEMY_ECHO'] = True
    return SQLAlchemy(app)
